SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TTSEMA_FLUJ_Q03]                
/*-------------------------------------------------------*/                
/*---Empresa               OFISIS S.A.                --*/                
/*---Cliente               CELIMA                     --*/                
/*---Sistema               Tesoreria                  --*/                
/*---M¢dulo                Empresa                    --*/                
/*---Programa              DESCRIPCION DE SEMANAS SEGUN CLASIFICACION (MIXTO) - REPORTE--*/                
/*---Script                SP_TCMOVI_BANC_Q03.sql     --*/                
/*---Nombre SP             SP_TCMOVI_BANC_Q03         --*/                
/*---Desarrollado por      Reynaldo Menacho Luyo      --*/                
/*---Fecha Creaci¢n        25/09/2001                 --*/                
/*---Base Datos            SQL SERVER        --*/                
/*---Versi¢n               2000                       --*/                
/*---Invoca a SP                                      --*/                
/*-------------------------------------------------------*/                
                
/*-------------------------------------------------------*/                
/*---Modificado 1 por           --*/                
/*---Fecha Modificaci¢n         --*/                 
/*---Detalle Modificaci¢n       --*/                
/*-------------------------------------------------------*/                
                
/*-------------------------------------------------------*/                
/*---Drop Proc SP_TTSEMA_FLUJ_Q03                      --*/                
/*---Grant execute on SP_TTSEMA_FLUJ_Q03 to public         --*/                 
/*---SP_TCMOVI_BANC_Q03 '01', 'DOL', 10, 2001, 11, 2001  --*/                
/*-------------------------------------------------------*/                
                
--EXEC UL_TTSEMA_FLUJ_Q15 '01', '2007/11/15', '2007/12/31'    
@ISCO_EMPR Varchar(2) ,                      
@IDFE_OPER_INIC TD_DT_001,                
@IDFE_OPER_FINA TD_DT_001     
/*,                
@INNU_MESE_FINA TD_IN_001,                
@INNU_ANIO_FINA TD_IN_001  */              
As                
CREATE TABLE #TWSEMA_FLUJ_Q01                
 ( CO_EMPR      varchar(3) NULL,                
   FE_SE01 VARCHAR(30)NULL,    
   FE_SE02 VARCHAR(30)NULL,    
   FE_SE03 VARCHAR(30)NULL,    
   FE_SE04 VARCHAR(30)NULL,    
   FE_SE05 VARCHAR(30)NULL,    
   FE_SE06 VARCHAR(30)NULL,    
   FE_SE07 VARCHAR(30)NULL,    
   FE_SE08 VARCHAR(30)NULL,    
   FE_SE09 VARCHAR(30)NULL,    
   FE_SE10 VARCHAR(30)NULL,    
   FE_SE11 VARCHAR(30)NULL,    
   FE_SE12 VARCHAR(30)NULL,    
   FE_SE13 VARCHAR(30)NULL,    
   FE_SE14 VARCHAR(30)NULL,    
   FE_SE15 VARCHAR(30)NULL,    
   FE_SE16 VARCHAR(30)NULL,    
   FE_SE17 VARCHAR(30)NULL,    
   FE_SE18 VARCHAR(30)NULL,    
   FE_SE19 VARCHAR(30)NULL,    
   FE_SE20 VARCHAR(30)NULL)                
            
                
DECLARE                
                
@VNNU_MESE  TD_IN_001,                
@VNNU_ANIO  TD_IN_001,                
@VSNU_MESE  TD_VC_002,                
@VSNU_ANIO  VARCHAR(4),                
@VNNU_CONT_SEMA TD_IN_001,                
@VSDE_SELE VARCHAR(6000),                
@VSCO_EMPR VARCHAR (50),                
@VSDE_CAMP VARCHAR(7) ,            
@VSCO_MONE_NACI TD_VC_003,            
@VSCO_MONE_EXTR TD_VC_003,          
@VNNU_FACT  NUMERIC(16,4),    
@VNNU_SEMA_ACTU TD_IN_001,     
@VSFE_OPER_INIC TD_VC_020,      
@VSFE_OPER_FINA TD_VC_020,      
@VSFE_INIC_SEMA TD_VC_020,      
@VSFE_FINA_SEMA TD_VC_020,    
@VNNU_SEMA_INIC TD_IN_001,    
@VNNU_SEMA_FINA TD_IN_001,    
@VSSIGN VARCHAR(20),    
@VSDE_REPO  VARCHAR(20)    
    
    
SELECT @VSFE_OPER_INIC = CONVERT(VARCHAR(10), @IDFE_OPER_INIC, 103)         
SELECT @VSFE_OPER_FINA = CONVERT(VARCHAR(10), @IDFE_OPER_FINA, 103)         
SELECT @VNNU_ANIO = YEAR(@IDFE_OPER_INIC)     
    
SELECT @VNNU_SEMA_INIC = NU_SEMA        
FROM TTSEMA_FLUJ        
WHERE CO_EMPR = @ISCO_EMPR        
AND NU_ANIO = YEAR(@IDFE_OPER_INIC)        
AND FE_INIC_SEMA < = Convert(Datetime, @VSFE_OPER_INIC, 103)          
AND FE_FINA_SEMA > = Convert(Datetime, @VSFE_OPER_INIC, 103)         
        
        
        
--SELECT *FROM TTSEMA_FLUJ        
        
SELECT @VNNU_SEMA_FINA = NU_SEMA        
FROM TTSEMA_FLUJ        
WHERE CO_EMPR = @ISCO_EMPR        
AND NU_ANIO = YEAR(@IDFE_OPER_FINA)        
AND FE_INIC_SEMA < = Convert(Datetime, @VSFE_OPER_FINA, 103)          
AND FE_FINA_SEMA > = Convert(Datetime, @VSFE_OPER_FINA, 103)         
    
    
    
IF @VNNU_SEMA_INIC > 10    
BEGIN    
 IF  10 - (@VNNU_SEMA_FINA - @VNNU_SEMA_INIC) > 0    
      SELECT @VNNU_SEMA_ACTU =  ((@VNNU_SEMA_INIC - 9) - (@VNNU_SEMA_FINA - @VNNU_SEMA_INIC - 1)) + 1    
 ELSE    
     SELECT @VNNU_SEMA_ACTU = @VNNU_SEMA_INIC - 9    
END    
ELSE    
BEGIN    
     SELECT @VNNU_SEMA_ACTU = 1    
END    
    
               
  INSERT INTO #TWSEMA_FLUJ_Q01            
  (CO_EMPR,  FE_SE01)                
   SELECT CO_EMPR,  REPLICATE( '0', 2-LEN(RTRIM(LTRIM(STR(NU_SEMA))))) + LTRIM(RTRIM(STR(NU_SEMA))) + REPLICATE( '0', 2-LEN(RTRIM(LTRIM(DAY(FE_INIC_SEMA))))) + LTRIM(RTRIM(DAY(FE_INIC_SEMA))) + '-' + SUBSTRING(DATENAME(MONTH,FE_INIC_SEMA),1,3) +' ' +     

   REPLICATE( '0', 2-LEN(RTRIM(LTRIM(DAY(FE_FINA_SEMA))))) + LTRIM(RTRIM(DAY(FE_FINA_SEMA))) + '-' + SUBSTRING(DATENAME(MONTH,FE_INIC_SEMA),1,3) + ' EJECUTADO'                    
   FROM TTSEMA_FLUJ    
   WHERE CO_EMPR = @ISCO_EMPR    
   AND NU_ANIO = @VNNU_ANIO    
   AND NU_SEMA = @VNNU_SEMA_ACTU    
          
    
SELECT @VNNU_CONT_SEMA = 2    
SELECT @VNNU_SEMA_ACTU = @VNNU_SEMA_ACTU + 1    
SELECT @VSSIGN = " + '-' + "    
    
     
SELECT @VSDE_CAMP = 'FE_SE' + REPLICATE('0', 2-LEN(RTRIM(LTRIM(STR(@VNNU_CONT_SEMA))))) + RTRIM(LTRIM(STR(@VNNU_CONT_SEMA)))                            
    
    
WHILE ( @VNNU_CONT_SEMA <= 20)             
BEGIN    
    
 --"' + '-' + '" SUBSTRING(DATENAME(MONTH,FE_INIC_SEMA),1,3)      
    
IF @VNNU_SEMA_INIC > 10    
BEGIN    
 IF  10 - (@VNNU_SEMA_FINA - @VNNU_SEMA_INIC) > 0    
   BEGIN    
      IF  @VNNU_CONT_SEMA <  10 + (10 - (@VNNU_SEMA_FINA - @VNNU_SEMA_INIC - 1))    
  SELECT @VSDE_REPO = " + ' EJECUTADO' "    
    ELSE    
      SELECT @VSDE_REPO = " + ' PROYECTADO' "    
   END    
 ELSE    
 BEGIN    
          IF @VNNU_CONT_SEMA < = 10    
    SELECT @VSDE_REPO = " + ' EJECUTADO' "    
     ELSE    
      SELECT @VSDE_REPO = " + ' PROYECTADO' "    
 END    
END    
ELSE    
BEGIN    
     IF @VNNU_CONT_SEMA <= @VNNU_SEMA_INIC     
                SELECT @VSDE_REPO = " + ' EJECUTADO' "    
     ELSE    
      SELECT @VSDE_REPO = " + ' PROYECTADO' "    
END    
    
    
    
    
EXECUTE('                
  UPDATE #TWSEMA_FLUJ_Q01            
  SET   ' + @VSDE_CAMP + '= REPLICATE( "0", 2-LEN(RTRIM(LTRIM(STR(T1.NU_SEMA))))) + LTRIM(RTRIM(STR(T1.NU_SEMA))) + REPLICATE( "0", 2-LEN(RTRIM(LTRIM(DAY(T1.FE_INIC_SEMA))))) + LTRIM(RTRIM(DAY(T1.FE_INIC_SEMA))) ' + @VSSIGN + ' SUBSTRING(DATENAME(MONTH,T1.FE_INIC_SEMA),1,3) ' +     
   " + ' ' + " + ' REPLICATE( "0", 2-LEN(RTRIM(LTRIM(DAY(T1.FE_FINA_SEMA))))) + LTRIM(RTRIM(DAY(T1.FE_FINA_SEMA))) ' + @VSSIGN + ' SUBSTRING(DATENAME(MONTH,T1.FE_FINA_SEMA),1,3) ' + @VSDE_REPO + '     
   FROM TTSEMA_FLUJ T1    
   WHERE T1.CO_EMPR = "' + @ISCO_EMPR + '"    
   AND T1.NU_ANIO = ' + @VNNU_ANIO + '    
   AND T1.NU_SEMA = ' + @VNNU_SEMA_ACTU +'    
')                
        
    
    
SELECT @VNNU_CONT_SEMA = @VNNU_CONT_SEMA + 1, @VNNU_SEMA_ACTU = @VNNU_SEMA_ACTU + 1                
               
  SELECT @VSDE_CAMP = 'FE_SE' + REPLICATE('0', 2-LEN(RTRIM(LTRIM(STR(@VNNU_CONT_SEMA))))) + RTRIM(LTRIM(STR(@VNNU_CONT_SEMA)))                
    
                        
END            
    
    
   
    
SELECT FE_SE01, FE_SE02, FE_SE03, FE_SE04, FE_SE05, FE_SE06, FE_SE07,     
FE_SE08, FE_SE09, FE_SE10, FE_SE11, FE_SE12, FE_SE13, FE_SE14,     
FE_SE15, FE_SE16, FE_SE17, FE_SE18, FE_SE19, FE_SE20    
FROM #TWSEMA_FLUJ_Q01    
        
RETURN          
                
/********************************* FIN ***********************************/


GO
